package com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.economicLossPE.repository.impl;

import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.entity.VillageTownHosPeoEntity;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.param.AssessParam;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.entity.CasualtiesPECalculation;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.economicLossPE.entity.EconomicLossPD;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.economicLossPE.entity.EconomicLossPDCalculation;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.economicLossPE.param.EconomicLossPECalculationParam;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.economicLossPE.repository.EconomicLossPDRepository;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.economicLossPE.vo.EconomicLossPCCPEVO;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.economicLossPE.vo.EconomicLossXYPEVO;
import com.css.fxfzfxqh.modules.largeScreen.entity.Calculation;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/06/16
 * @Description: 概率评估_经济损失Repository
 */
@Repository
public class EconomicLossPDRepositoryImpl implements EconomicLossPDRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    //抗震能力_正确评估_评估记录主表
    private static final String TABLE_NAME = "fxqh_economic_loss_assess_pd";
    //抗震能力_正确评估_评估记录_计算(烈度,概率)(地理id,房屋单体id) 合并为一个字段
    private static final String TABLE_CALCULATION = "fxqh_economic_loss_calculation_pd";

    //新增评估记录
    @Override
    public void addAssess(EconomicLossPD param) {
        String sql = "insert into " + TABLE_NAME + "(id,assess_code,assess_name,assess_desc,house_data,house_data_flag," +
                "coordinate_range,calculation,create_user,create_time,del_flag,seismic_input_mode) " +
                "values(:id,:assessCode,:assessName,:assessDesc,:houseData,:houseDataFlag," +
                ":coordinateRange,:calculation,:createUser,:createTime,:delFlag,:seismicInputMode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //根据id查询
    @Override
    public EconomicLossPD findById(String id) {
        String sql = "select * from " + TABLE_NAME + " where id = ? ";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EconomicLossPD.class), id);
    }

    //修改评估记录
    @Override
    public void upDateAssess(EconomicLossPD param) {
        String sql = "update " + TABLE_NAME +
                " set assess_name = :assessName,assess_desc = :assessDesc," +
                "update_user=:updateUser,update_time=:updateTime where del_flag = '0' and id = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //查询最近一次code
    @Override
    public String queryLatest(StringBuilder code) {
        StringBuilder sql = new StringBuilder("select assess_code from " + TABLE_NAME + " where del_flag = '0' and assess_code like '%" + code + "%' order by create_time desc limit 1");
        try {
            return jdbcTemplate.queryForObject(sql.toString(), String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //分页
    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, AssessParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by geography_id ");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //评估记录编号
        if (!PlatformObjectUtils.isEmpty(param.getAssessCode())) {
            whereSql.append(" and assess_code = '" + param.getAssessCode() + "'");
        }
        //评估记录名称
        if (!PlatformObjectUtils.isEmpty(param.getAssessName())) {
            whereSql.append(" and assess_name = '" + param.getAssessName() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EconomicLossPD.class)).size();
        List<EconomicLossPD> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(EconomicLossPD.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //批量新增计算值
    @Override
    public void batchCalculation(List<EconomicLossPDCalculation> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,extreme,calculated_value,correction_value,expect_value,coordinate_range,create_user,create_time,years," +
                "house_data_flag,seismic_input_mode,risk,extends1,geom,del_flag)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:geographyId,:extreme,:calculatedValue,:correctionValue,:expectValue,:coordinateRange,:createUser,:createTime,:years," +
                ":houseDataFlag,:seismicInputMode,:risk,:extends1,st_geomFromText(:geom,4490),:delFlag)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    //省市县
    @Override
    public List<EconomicLossPCCPEVO> getListPCC(EconomicLossPECalculationParam param) {
        StringBuilder sql = new StringBuilder("select province_name,city_name,county_name,geography_id," +
                "case extreme when 'pgcd1' then '基本完好'" +
                " when 'pgcd2' then '轻微破坏' " +
                " when 'pgcd3' then '中等破坏' " +
                " when 'pgcd4' then '严重破坏' " +
                " when 'pgcd5' then '倒塌' end extreme ," +
                "calculated_value,correction_value,expect_value from " + TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        //T年
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            whereSql.append(" and years = '" + param.getYears() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())){
            whereSql.append(" and province_name = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())){
            whereSql.append(" and city_name = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())){
            whereSql.append(" and county_name = '" + param.getCounty() + "'");
        }
        return jdbcTemplate.query(sql.append(whereSql.append(" order by geography_id,extreme")).toString(),
                new BeanPropertyRowMapper<>(EconomicLossPCCPEVO.class));
    }

    //坐标范围
    @Override
    public List<EconomicLossXYPEVO> getListXY(EconomicLossPECalculationParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        //T年
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            whereSql.append(" and years = '" + param.getYears() + "'");
        }
        return jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(EconomicLossXYPEVO.class));
    }

    //查询计算值
    @Override
    public Map<String, Object> queryCalculation(int curPage, int pageSize, String id, String years) {
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder sql = new StringBuilder("select * from " + TABLE_CALCULATION + " where del_flag= '0' ");
        //任务id
        if (!PlatformObjectUtils.isEmpty(id)) {
            sql.append(" and assess_id = '" + id + "'");
        }
        //年份
        if (!PlatformObjectUtils.isEmpty(years)) {
            sql.append(" and years = '" + years + "'");
        }
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EconomicLossPDCalculation.class)).size();
        List<EconomicLossPDCalculation> list = jdbcTemplate.query(sql.append(" order by geography_id,extreme ").append(limitSql).toString(), new BeanPropertyRowMapper<>(EconomicLossPDCalculation.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //修改概率P(t)修正值
    @Override
    public void updateCorrectionValue(EconomicLossPDCalculation param) {
        String sql = "update " + TABLE_CALCULATION + " set correction_value=:correctionValue , update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void deleteCalculation(String assessId) {
        jdbcTemplate.update("delete from " + TABLE_CALCULATION + " where assess_id='" + assessId + "'");
    }

    @Override
    public List<Map<String, Object>> getSumByDivisionAndAssessCodes(String province, String city, String county,
                                                                    String assessCodes, String condition, String seiMode) {
        String sql = "SELECT SUM(CAST(t1.chance_pt AS FLOAT)) AS SUM, COUNT(t1.assess_id) AS COUNT" +
                " FROM fxqh_economic_loss_calculation_pd t1 LEFT JOIN ( SELECT * FROM fxqh_calculation_tasks WHERE del_flag = '0' AND id='" + assessCodes + "') t2 ON t1.assess_id = t2.ID" +
                " WHERE t1.province_name = ? AND t1.city_name = ? AND t1.county_name = ? AND t1.years = ? AND t2.del_flag = '0'" +
                " and seismic_input_mode= ?" +
                " GROUP BY t1.extends1 ORDER BY COUNT(t1.assess_id) DESC";
        return jdbcTemplate.queryForList(sql, province, city, county, condition, seiMode);
    }

    @Override
    public List<Map<String, Object>> getSumByTaskId(String taskId) {
        String sql = "SELECT SUM(CAST(t1.correction_value AS FLOAT)) AS SUM, COUNT(t1.assess_id) AS COUNT, t1.province_name as provinceName, t1.city_name as cityName,t1.county_name as countyName, t1.years as years " +
                " FROM fxqh_economic_loss_calculation_pd t1" +
                " WHERE t1.assess_id = '" + taskId + "'" +
                " GROUP BY t1.province_name, t1.city_name,t1.county_name, t1.years,t1.extends1 ORDER BY COUNT(t1.assess_id) DESC";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<EconomicLossPDCalculation> getByTaskId(String taskId) {
        String sql = "SELECT * FROM fxqh_economic_loss_calculation_pd t1  WHERE t1.assess_id = '" + taskId + "'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(EconomicLossPDCalculation.class));
    }

    @Override
    public VillageTownHosPeoEntity findByProvinceAndCity(String ppName, String cityName, String prefectureName, String villageName) {
        String sql = "select * from data_village_town_hos_peo where is_delete='0' and province='"+ppName+"' and city = '"+cityName+"'";
        if(StringUtils.isNotBlank(prefectureName)){
            sql+=" and prefecture='"+prefectureName+"'";
        }
        List<VillageTownHosPeoEntity> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(VillageTownHosPeoEntity.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):null;
    }

    @Override
    public List<Calculation> getValue(List<String> taskIdList, String code) {
        String sql = "";
        if("0".equals(code)){
            sql = "select sum(cast(correction_value as DEC(10,4)))/10000 val,province_name areaName from fxqh_economic_loss_calculation_pd where assess_id in ('"+String.join("','", taskIdList)+"') group by province_name order by val";
        }else{
            sql = "select sum(cast(correction_value as DEC(10,4)))/10000 val,province_name areaName from fxqh_economic_loss_calculation_pd where assess_id in ('"+String.join("','", taskIdList)+"')  group by province_name order by val";
        }
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Calculation.class));
    }
}
